CREATE TABLE [dbo].[tb_GrillaFunciones]
(
[idGrillaFuncion] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Norma] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaBaja] [datetime] NULL,
[Jornada] [int] NOT NULL,
[TipoDeOfertaEducativa] [int] NOT NULL,
[Categoria] [int] NOT NULL,
[CategorizacionMaxSecc] [int] NULL,
[CategorizacionMinSecc] [int] NULL,
[MatriculaMin1erSecc] [int] NULL,
[CantMatriculaMin1erSecc] [int] NULL,
[MatriculaMinPorSecc] [int] NULL,
[MatriculaMaxPorSecc] [int] NULL,
[M2MinPorAlumno] [float] NULL,
[M2OptimoPorAlumno] [float] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_GrillaFunciones] ON [dbo].[tb_GrillaFunciones]
FOR UPDATE AS
SET NOCOUNT ON
if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_GrillaFunciones set FechaBaja = getdate() where idGrillaFuncion=(Select idGrillaFuncion from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')	UPDATE tb_GrillaFunciones set FechaBaja= null where idGrillaFuncion=(Select idGrillaFuncion from inserted)

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_GrillaFunciones] ON [dbo].[tb_GrillaFunciones]
 FOR UPDATE AS 
SET NOCOUNT ON
 IF (update(idGrillaFuncion)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_FuncionesDeGrilla
		 SET Grilla = (SELECT idGrillaFuncion FROM inserted)
		 WHERE tb_FuncionesDeGrilla.Grilla = (SELECT idGrillaFuncion FROM deleted)
		 UPDATE tb_SubOrganizaciones
		 SET Grilla = (SELECT idGrillaFuncion FROM inserted)
		 WHERE tb_SubOrganizaciones.Grilla = (SELECT idGrillaFuncion FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end

GO
ALTER TABLE [dbo].[tb_GrillaFunciones] ADD CONSTRAINT [PK_tb_GrillaFunciones] PRIMARY KEY NONCLUSTERED  ([idGrillaFuncion]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_GrillaFunciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_GrillaFunciones_tb_CategoriasDeSuborganizaciones] FOREIGN KEY ([Categoria]) REFERENCES [dbo].[tb_CategoriasDeSuborganizaciones] ([IdCategoria])
GO
ALTER TABLE [dbo].[tb_GrillaFunciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_GrillaFunciones_tb_Jornadas] FOREIGN KEY ([Jornada]) REFERENCES [dbo].[tb_Jornadas] ([idJornada])
GO
ALTER TABLE [dbo].[tb_GrillaFunciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_GrillaFunciones_tb_Criterios1] FOREIGN KEY ([MatriculaMaxPorSecc]) REFERENCES [dbo].[tb_Criterios] ([idCriterio])
GO
ALTER TABLE [dbo].[tb_GrillaFunciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_GrillaFunciones_tb_Criterios] FOREIGN KEY ([MatriculaMin1erSecc]) REFERENCES [dbo].[tb_Criterios] ([idCriterio])
GO
ALTER TABLE [dbo].[tb_GrillaFunciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_GrillaFunciones_tb_TiposOrganizacion] FOREIGN KEY ([TipoDeOfertaEducativa]) REFERENCES [dbo].[tb_TiposOrganizacion] ([idTipoOrganizacion])
GO
EXEC sp_bindrule N'[dbo].[S-N]', N'[dbo].[tb_GrillaFunciones].[Baja]'
GO
